﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Xml;

namespace SqlMeth
{
    public class SqlMetalWrapper : IToolWrapper
    {
        SqlMetalSettings _settings = null;
        SqlConnection _connection = null;

        /// <summary>
        /// Initializes object
        /// </summary>
        public SqlMetalWrapper(SqlMetalSettings settings, string connection)
        {
            _settings = settings;
            _connection = new SqlConnection(connection);
        }

        /// <summary>
        /// Returns the name of the connection's database.
        /// </summary>
        public string DatabaseName
        {
            get { return _connection.Database; }
        }

        /// <summary>
        /// Returns the path to the DBML that should be deleted or created.
        /// </summary>
        public string ModelFilePath
        {
            get
            {
                return _settings.DbmlPath + "\\" + DatabaseName + ".dbml";
            }
        }

        /// <summary>
        /// Deletes a DBML with the database's name and extension DBML.
        /// </summary>
        /// <returns>True if deleted.</returns>
        public bool DeleteOldModel()
        {
            System.IO.File.Delete(ModelFilePath);

            return !System.IO.File.Exists(ModelFilePath);
        }

        /// <summary>
        /// Builds the arguments for SqlMetal using the configuration settings.
        /// </summary>
        /// <returns>A string of SqlMetal arguments.</returns>
        string BuildArgumentsForSqlMetal()
        {
            //--------------------------- TO DO: Better reflect arguments like in MS documentation
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat("/conn:\"{0}\"", _connection.ConnectionString);

            if (_settings.Views)
                builder.Append(" /views");
            if (_settings.Functions)
                builder.Append(" /functions");
            if (_settings.StoredProcedures)
                builder.Append(" /sprocs");

            builder.AppendFormat(" /dbml:\"{0}\"", ModelFilePath);
            builder.AppendFormat(" /language:{0}", _settings.Language);

            return builder.ToString();
        }

        /// <summary>
        /// Starts the SqlMetal.exe as specified in the arguments.
        /// </summary>
        public void ExecuteTool()
        {
            Process sqlMetal = new Process();
            sqlMetal.StartInfo.FileName = _settings.Path;
            sqlMetal.StartInfo.Arguments = BuildArgumentsForSqlMetal();
            sqlMetal.Start();
            sqlMetal.WaitForExit();
        }

        /// <summary>
        /// Creates an XML node to insert into DBML "Table" node for operations like INSERT, UPDATE or DELETE.
        /// </summary>
        /// <param name="nodeName">Name of the node to create</param>
        /// <param name="id">Identifier of the node</param>
        /// <param name="nsmgr">Namespace manager for selecting nodes</param>
        /// <param name="sProcNode">Stored procedure node in DBML</param>
        /// <param name="tableNode">Table node in DBML</param>
        /// <param name="doc">Loaded DBML</param>
        /// <returns>A fully formed table function node</returns>
        XmlElement CreateTableFunctionElement(string nodeName, string id, string nsmgr, XmlNode sProcNode, XmlNode tableNode, ref XmlDocument doc)
        {
            // Create element. Example: <InsertFunction
            XmlElement tableFunction = doc.CreateElement(nodeName, nsmgr);

            // Example: <InsertFunction FunctionId="FunctionId1">
            XmlAttribute functionId = doc.CreateAttribute("FunctionId");
            functionId.Value = id;

            // Effectively joins the stored procedure with the table function
            tableFunction.Attributes.SetNamedItem(functionId);

            // Get stored procedures' parameters
            IEnumerable<XmlNode> sProcArguments = from XmlNode spn in sProcNode.ChildNodes
                                                  where spn.Name == "Parameter"
                                                  select spn;
            foreach (XmlNode sProcArg in sProcArguments)
            {
                XmlElement argument = doc.CreateElement("Argument", nsmgr);

                // Pass the stored procedure's parameters to argument on tableFunction
                XmlAttribute parameter = doc.CreateAttribute("Parameter");
                // Parameter values can be on either the "Parameter" attribute, when value's first letter is uppercase,
                // or the "Name" attribute, when first letter isn't uppercase
                if (sProcArg.Attributes["Parameter"] != null)
                    parameter.Value = sProcArg.Attributes["Parameter"].Value;
                else
                    parameter.Value = sProcArg.Attributes["Name"].Value;

                // Attach parameter attribute to Argument
                // Example: <Argument Parameter="iD"...
                argument.Attributes.SetNamedItem(parameter);

                XmlAttribute member = doc.CreateAttribute("Member");
                // Obtain member value from table. First, look by "Column"s that best match the arguments for the stored procedure, then
                // those who have a "Member" or "Name" attribute who's best match the name of the stored procedure argument.
                // If no "Member" attribute, use "Name".
                // Find column element in table
                
                // Note on the LINQ: For some reason "_CreatedBy" on the stored procedure comes into the DBML as NVarchar(1).
                // This can't be matched with the same column on the table with NVarChar(64). For that reason,
                // I am opting to use substrings.

                /*
                string sProcArgDbType = sProcArg.Attributes["DbType"].Value;
                
                if (sProcArgDbType == "NVarChar(1)")
                    sProcArgDbType = sProcArg.Attributes["DbType"].Value.Substring(0, sProcArg.Attributes["DbType"].Value.IndexOf('('));
                */
                XmlNode matchColNode = (from XmlNode spn in tableNode.ChildNodes[0].ChildNodes
                                        where spn.Attributes["Type"].Value == sProcArg.Attributes["Type"].Value &&
                                        spn.Attributes["DbType"].Value.Contains(sProcArg.Attributes["DbType"].Value) &&
                                        ((spn.Attributes["Member"] != null && spn.Attributes["Member"].Value.ToLower() == sProcArg.Attributes["Name"].Value.ToLower()) ||
                                        (spn.Attributes["Name"] != null && spn.Attributes["Name"].Value.ToLower() == sProcArg.Attributes["Name"].Value.ToLower()))
                                        select spn).Single();

                if (matchColNode.Attributes["Member"] != null)
                {
                    if (matchColNode.Attributes["Member"].Value.ToLower() == sProcArg.Attributes["Name"].Value.ToLower())
                    {
                        member.Value = matchColNode.Attributes["Member"].Value;
                    }
                }
                else
                {
                    if (matchColNode.Attributes["Name"] != null)
                    {
                        if (matchColNode.Attributes["Name"].Value.ToLower() == sProcArg.Attributes["Name"].Value.ToLower())
                        {
                            member.Value = matchColNode.Attributes["Name"].Value;
                        }
                    }
                    else
                    {
                        throw new FormatException(String.Format("Column \"{0}\" in table \"{1}\" has no \"Member\" or \"Name\" attributes",
                            matchColNode.Name, tableNode.Attributes["Name"].Value));
                    }
                }

                // Add "Member" attribute
                // Example: <Argument Parameter="iD" Member="ID" />
                argument.Attributes.SetNamedItem(member);
                // Example:
                //  <InsertFunction FunctionId="FunctionId1">
                //      <Argument Parameter="iD" Member="ID" />
                //      ...
                //  </InsertFunction>
                tableFunction.AppendChild(argument);
            }

            // Ready to attach after </Type> in <Table>
            return tableFunction;
        }

        /// <summary>
        /// Modifies a SqlMetal generated file to wire a table for CRUD using
        /// the generated stored procedures.
        /// </summary>
        public void WireProceduresToTables(List<DbTable> tables)
        {
            // Note: Need to tidy up this code...
            int functionCount = 1;

            // Open DBML file
            XmlDocument doc = new XmlDocument();
            doc.Load(ModelFilePath);
            XmlNamespaceManager nsmgr = new XmlNamespaceManager(doc.NameTable);
            nsmgr.AddNamespace("db", _settings.DbmlNamespace);

            // For each table, associate insert, update and delete functions
            foreach (DbTable table in tables)
            {
                // Find table node
                string xPath = String.Format("//db:Table[@Name=\'{0}.{1}\']", table.Schema, table.Name);
                XmlNode tableNode = doc.SelectSingleNode(xPath, nsmgr);

                // Search for INSERT stored procedure
                xPath = String.Format("//db:Function[@Name=\'{0}\']", table.Insert.ToString());
                XmlNode insertSProc = doc.SelectSingleNode(xPath, nsmgr);

                // Create attribute 'Id' with value "FunctionIdX"
                XmlAttribute insertId = doc.CreateAttribute("Id");
                insertId.Value = string.Format("FunctionId{0}", functionCount);
                functionCount++;

                // Append attribute to insertSProc
                insertSProc.Attributes.SetNamedItem(insertId);

                // Create InsertFunction node to insert into table
                tableNode.AppendChild(CreateTableFunctionElement("InsertFunction", insertId.Value, _settings.DbmlNamespace, insertSProc, tableNode, ref doc));

                // Search for UPDATE stored procedure
                xPath = String.Format("//db:Function[@Name=\'{0}\']", table.Update.ToString());
                XmlNode updateSProc = doc.SelectSingleNode(xPath, nsmgr);

                // Create attribute 'Id' with value "FunctionIdX"
                XmlAttribute updateId = doc.CreateAttribute("Id");
                updateId.Value = string.Format("FunctionId{0}", functionCount);
                functionCount++;

                // Append attribute to updateSProc
                updateSProc.Attributes.SetNamedItem(updateId);

                // Create UpdateFunction node to update into table
                tableNode.AppendChild(CreateTableFunctionElement("UpdateFunction", updateId.Value, _settings.DbmlNamespace, updateSProc, tableNode, ref doc));

                // Search for DELETE stored procedure
                xPath = String.Format("//db:Function[@Name=\'{0}\']", table.Delete.ToString());
                XmlNode deleteSProc = doc.SelectSingleNode(xPath, nsmgr);

                // Create attribute 'Id' with value "FunctionIdX"
                XmlAttribute deleteId = doc.CreateAttribute("Id");
                deleteId.Value = string.Format("FunctionId{0}", functionCount);
                functionCount++;

                // Append attribute to deleteSProc
                deleteSProc.Attributes.SetNamedItem(deleteId);

                // Create DeleteFunction node to insert into table
                tableNode.AppendChild(CreateTableFunctionElement("DeleteFunction", deleteId.Value, _settings.DbmlNamespace, deleteSProc, tableNode, ref doc));
            }

            // Save changes
            doc.Save(ModelFilePath);
        }

        public void ModifyReturnTypeOfReadSProcs(List<DbTable> tables)
        {
            // For each table's read stored procedures, identify the table's "Type" element with attribute "Id"
            // and then clean the stored procedure's "ElementType" attributes replacing them only by "IdRef", which
            // matches the one on the table. Finally, "ElementType" on stored procedure should have no child nodes.
            string idFormat = "ID{0}";
            int counter = 1;
            
            XmlDocument doc = new XmlDocument();
            doc.Load(ModelFilePath);
            XmlNamespaceManager nsmgr = new XmlNamespaceManager(doc.NameTable);
            nsmgr.AddNamespace("db", _settings.DbmlNamespace);

            foreach(DbTable table in tables)
            {
                string xPath = null;

                foreach (DbSProc sProc in table.ReadProcedures)
                {
                    // Find "ElementType" in "Function", stored procedure, node
                    xPath = String.Format("//db:Function[@Name=\'{0}.{1}\']/db:ElementType", table.Schema, sProc.Name);
                    XmlElement elementTypeNode = doc.SelectSingleNode(xPath, nsmgr) as XmlElement;
                    
                    // Clear node
                    elementTypeNode.RemoveAll();

                    // Prevent node from having a closing tag
                    elementTypeNode.IsEmpty = true;

                    // Add "IdRef" attribute to bind with a table's "Type"
                    XmlAttribute idRefAttribute = doc.CreateAttribute("IdRef");
                    idRefAttribute.Value = String.Format(idFormat, counter);

                    // Append "IdRef" to "ElementType". Example:
                    // <Function Name="dbo...
                    //      <ElementType IdRef="ID1" />
                    // </Function>
                    elementTypeNode.Attributes.SetNamedItem(idRefAttribute);
                }

                // Tie the return definition of the stored procedure to the "Type" of the table
                xPath = String.Format("//db:Table[@Name=\'{0}.{1}\']/db:Type", table.Schema, table.Name);
                XmlNode tableTypeNode = doc.SelectSingleNode(xPath, nsmgr);

                // Add "Id" attribute to bind with stored procedures
                XmlAttribute idAttribute = doc.CreateAttribute("Id");
                idAttribute.Value = String.Format(idFormat, counter);

                // Append "Id" to "Type". Example:
                // <Table Name="dbo...
                //      <Type Id="ID1">
                //           ...
                //      </Type>
                //      ...
                // </Table>
                tableTypeNode.Attributes.SetNamedItem(idAttribute);
                counter++;
            }

            // Save changes
            doc.Save(ModelFilePath);
        }
    }
}
